package Dao.Student.Imp;

import Dao.Student.StudentTitleDao;
import Entity.Role.Teacher;
import Entity.TitleAndPaper.Title;
import Util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentTitleDaoImp implements StudentTitleDao {
    private PreparedStatement ps = null;
    private ResultSet rs = null;

    /**
     * 根据学号获取学生课题信息
     */
    @Override
    public List<Title> getTitleInfo(String sno,Integer offset,Integer limit, Connection conn) {
        List<Title> titles = new ArrayList<>();
        String sql = "select xt_id,title,intr,`from`,categoty,nature,addtime,no,state,college_name,major_name,opinoin,is_pass,tname,tno,sname \n" +
                "from title where no = ? limit ?,?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1, sno);
            ps.setInt(2, offset);
            ps.setInt(3, limit);
            rs = ps.executeQuery();
            while (rs.next()) {
                Title title = new Title();
                title.setXtId(Integer.parseInt(rs.getString("xt_id")));
                title.setTitle(rs.getString("title"));
                title.setIntr(rs.getString("intr"));
                title.setFrom(rs.getString("from"));
                title.setCategoty(rs.getString("categoty"));
                title.setNature(rs.getString("nature"));
                title.setAddtime(rs.getString("addtime"));
                title.setNo(rs.getString("no"));
                title.setState(rs.getString("state"));
                title.setCollegeName(rs.getString("college_name"));
                title.setMajorName(rs.getString("major_name"));
                title.setOpinoin(rs.getString("opinoin"));
                title.setIsPass(rs.getString("is_pass"));
                title.setTName(rs.getString("tname"));
                title.setTno(rs.getString("tno"));
                title.setSname(rs.getString("sname"));
                //System.out.println(title);
                titles.add(title);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return titles;
    }


    /**
     * 学生获取可选课题
     */
    @Override
    public List<Title> titleSelect(String sno, String keyword,Integer offset,Integer limit,Connection conn) {
        List<Title> titles = new ArrayList<>();
        String sql = "select xt_id,title,intr,`from`,categoty,nature,addtime,no,state,college_name,major_name,opinoin,is_pass,tname,tno,sname " +
                "from title where  applly = '老师' and is_pass= '通过' and no =(select tno from stu_info where sno = ?) and title like ? limit ?,?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setObject(1, sno);
            ps.setObject(2, keyword);
            ps.setInt(3, offset);
            ps.setInt(4, limit);
            rs = ps.executeQuery();
            while (rs.next()) {
                Title title = new Title();
                title.setXtId(Integer.parseInt(rs.getString("xt_id")));
                title.setTitle(rs.getString("title"));
                title.setIntr(rs.getString("intr"));
                title.setFrom(rs.getString("from"));
                title.setCategoty(rs.getString("categoty"));
                title.setNature(rs.getString("nature"));
                title.setAddtime(rs.getString("addtime"));
                title.setNo(rs.getString("no"));
                title.setState(rs.getString("state"));
                title.setCollegeName(rs.getString("college_name"));
                title.setMajorName(rs.getString("major_name"));
                title.setOpinoin(rs.getString("opinoin"));
                title.setIsPass(rs.getString("is_pass"));
                title.setTName(rs.getString("tname"));
                title.setTno(rs.getString("tno"));
                title.setSname(rs.getString("sname"));
                //System.out.println(title);
                titles.add(title);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return titles;
    }


}
